# coding=utf-8
import os

import pandas as pd
from openpyxl.reader.excel import load_workbook
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://test123:test1234@192.168.3.12:3306/map_device_his0813")

# device_list = ['GW4037',
# 		'GW537A',
# 		'GW52C0',
# 		'GW418F',
# 		'GW1939',
# 		'GW52C6',
# 		'GW159B',
# 		'GW5286',
# 		'GW4044',
# 		'GW4920',
# 		'GW5226',
# 		'GW5369',
# 		'GW440E',
# 		'GW58A7',
# 		'GW41D4',
# 		'GW174B',
# 		'GW538F',
# 		'GW19F4',
# 		'GW4E33',
# 		'GW4D64',
# 		'GW19A9',
# 		'GW4EC8',
# 		'GW4526',
# 		'GW4459',
# 		'GW19EF',
# 		'GW5890',
# 		'GW4DAD',
# 		'GW1999',
# 		'GW17B4',
# 		'GW495A',
# 		'GW47F2']
#
#
#
# # Excel文件路径
# excel_path = 'result_heigh_new.xlsx'
#
#
#
# for device in device_list:
#     # 2. 使用SQL查询读取数据
#     query = "SELECT device_id,update_time,pa FROM xiaxiatest_his_all_new25 WHERE device_id=\"{}\"".format(device)  # 替换为你的表名和字段
#     df = pd.read_sql_query(query, engine)
#     # 检查Excel文件是否存在以及最大列数
#     #excel_path = 'result_pa.xlsx'
#
#     # 检查文件是否存在
#     if os.path.exists(excel_path):
#         # 加载现有的Excel文件
#         book = load_workbook(filename=excel_path)
#         # 假设我们要写入的是第一个工作表，也可以根据需要选择其他工作表
#         sheet = book.active
#         # 获取最大的列号
#         max_column = sheet.max_column
#         mode = 'a'  # 追加模式
#         if_sheet_exists = 'overlay'  # 已存在工作表时，追加数据
#         book.close()  # 关闭工作簿以释放资源
#     else:
#         mode = 'w'  # 写入模式
#         if_sheet_exists = None  # 不需要设置，因为工作表不存在
#         # 文件不存在，设置最大列号为0
#         max_column = 0
#
#     # 确定新列的列号
#     new_column_number = max_column + 1
#
#     # 写入Excel文件，如果文件不存在，ExcelWriter将创建它
#     with pd.ExcelWriter(excel_path, engine='openpyxl',mode=mode,if_sheet_exists = if_sheet_exists ) as writer:
#         # 写入DataFrame到Excel的下一个空白列
#         df.to_excel(writer, index=False, startcol=new_column_number, sheet_name='Sheet1')
#
#     print("数据已成功写入Excel文件。")



